USE [ProjetoComperio]
GO

/*Controller*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Controller](
	[ControllerID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
	[Description] [text] NOT NULL,
 CONSTRAINT [PK_Controller] PRIMARY KEY CLUSTERED 
(
	[ControllerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/* Action */
CREATE TABLE [dbo].[Action](
	[ActionID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
	[ControllerID] [int] NOT NULL,
 CONSTRAINT [PK_Action] PRIMARY KEY CLUSTERED 
(
	[ActionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Action]  WITH CHECK ADD  CONSTRAINT [FK_Action_Controller] FOREIGN KEY([ControllerID])
REFERENCES [dbo].[Controller] ([ControllerID])
GO

ALTER TABLE [dbo].[Action] CHECK CONSTRAINT [FK_Action_Controller]
GO

/*Role*/
CREATE TABLE [dbo].[Role](
	[RoleID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/*RoleAction*/
CREATE TABLE [dbo].[RoleAction](
	[RoleActionID] [int] IDENTITY(1,1) NOT NULL,
	[RoleID] [int] NOT NULL,
	[ActionID] [int] NOT NULL,
 CONSTRAINT [PK_RoleAction] PRIMARY KEY CLUSTERED 
(
	[RoleActionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleAction]  WITH CHECK ADD  CONSTRAINT [FK_RoleAction_Action] FOREIGN KEY([ActionID])
REFERENCES [dbo].[Action] ([ActionID])
GO

ALTER TABLE [dbo].[RoleAction] CHECK CONSTRAINT [FK_RoleAction_Action]
GO

ALTER TABLE [dbo].[RoleAction]  WITH CHECK ADD  CONSTRAINT [FK_RoleAction_Role] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Role] ([RoleID])
GO

ALTER TABLE [dbo].[RoleAction] CHECK CONSTRAINT [FK_RoleAction_Role]
GO

/*User*/
CREATE TABLE [dbo].[User](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[PasswordQuestion] [varchar](50) NOT NULL,
	[PasswordAnswer] [varchar](50) NOT NULL,
	[IsApproved] [bit] NOT NULL,
	[LastActivityDate] [datetime] NOT NULL,
	[LastLoginDate] [datetime] NOT NULL,
	[LastPasswordChangedDate] [datetime] NOT NULL,
	[CreationDate] [datetime] NOT NULL,
	[IsOnLine] [bit] NOT NULL,
	[IsLockedOut] [bit] NOT NULL,
	[LastLockedOut] [bit] NOT NULL,
	[LastLockedOutDate] [datetime] NOT NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL,
	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
	[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
	[Email] [varchar](50) NOT NULL,
	[RoleID] [int] NOT NULL,
	[Comment] [text] NOT NULL,
	[PersonID] [int] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Person]
GO

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Role] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Role] ([RoleID])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Role]
GO

/*ExceptionLogging*/
CREATE TABLE [dbo].[ExceptionLogging](
	[ExceptionLoggingID] [int] IDENTITY(1,1) NOT NULL,
	[Message] [text] NOT NULL,
	[Source] [text] NOT NULL,
	[StackTrace] [text] NOT NULL,
	[TargetSite] [text] NOT NULL,
	[Date] [datetime] NOT NULL,
	[UserName] [varchar](250) NOT NULL,
	[UserAgent] [text] NOT NULL,
	[URL] [varchar](250) NOT NULL,
 CONSTRAINT [PK_ExceptionLogging] PRIMARY KEY CLUSTERED 
(
	[ExceptionLoggingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO









